{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 独立使用django model（一）\n",
    "# Django Shell\n",
    "# 项目根目录中打开Django Shell : python manage.py shell\n",
    "# import django\n",
    "# django.setup()  # 装载Django\n",
    "\n",
    "# 独立使用django model（二）\n",
    "# python ×××.py or Jupyter Notebook\n",
    "# 将脚本放置在项目根目录下jupyter_notebook文件中比较方便\n",
    "import sys\n",
    "import os\n",
    "import json\n",
    "import requests\n",
    "from django.core.files import File\n",
    "from django.core.files.base import ContentFile\n",
    "# 外部脚本链接django项目\n",
    "# 添加环境变量\n",
    "'''\n",
    "print(os.path.abspath('__file__'))\n",
    "print(os.path.dirname(os.path.abspath('__file__')))\n",
    "print(os.path.dirname(os.path.dirname(os.path.abspath('__file__'))))\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis/jupyter_notebook/__file__\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis/jupyter_notebook\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis\n",
    "'''\n",
    "project = os.path.dirname(os.getcwd())  # get current work directory\n",
    "sys.path.append(project)\n",
    "sys.path.append(os.path.join(project, 'rmis'))\n",
    "# sys.path.append已设置临时环境变量，可以直接调用其中的文件，脚本是外部脚本，只是放在了项目当中而已\n",
    "os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'settings')\n",
    "# 相关数据库配置等都在settings.py文件中\n",
    "# 导入并装载django\n",
    "import django\n",
    "django.setup()\n",
    "# 脚本正文"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1562.0, 1506.0, 1528.0, 1560.0]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from user.models import Clerk\n",
    "from invoice.models import Sale, GoodInSale\n",
    "from django.forms import model_to_dict\n",
    "\n",
    "clerks = [ \n",
    "    model_to_dict(clerk) for clerk in Clerk.objects.filter()\n",
    "]\n",
    "# id created fk_store\n",
    "sales = [\n",
    "    model_to_dict(sale) for sale in Sale.objects.filter()\n",
    "]\n",
    "sales\n",
    "# id amount interests \n",
    "\n",
    "sale_item_list = [\n",
    "    model_to_dict(sale_item) for sale_item in GoodInSale.objects.filter()\n",
    "]\n",
    "sale_item_list \n",
    "# fk_sale fk_quantity \n",
    "\n",
    "df_sale_item = pd.DataFrame.from_records(sale_item_list)\n",
    "df_sale_quantity = df_sale_item.groupby(\"fk_sale\").sum()[[\"quantity\"]]\n",
    "\n",
    "df_sale = pd.DataFrame.from_records(sales)\n",
    "df_sale = df_sale.join(df_sale_quantity, on=\"id\")[[\"id\",\"amount\",\"interests\",\"quantity\",\"created\",\"fk_clerk\"]].dropna()\n",
    "df_sale\n",
    "\n",
    "df_clerk = pd.DataFrame.from_records(clerks)[[\"id\",\"fk_store\"]]\n",
    "df_clerk = df_clerk.set_index(\"id\")\n",
    "df_clerk \n",
    "df_sale = df_sale.join(df_clerk, on=\"fk_clerk\")\n",
    "df_sale\n",
    "\n",
    "\n",
    "grouped = df_sale.groupby(\"fk_clerk\")\n",
    "df_sale_des = grouped.describe().sort_index()\n",
    "\n",
    "categories = df_sale_des.index.to_list()\n",
    "\n",
    "df_sale_des\n",
    "\n",
    "df_sale_des = df_sale_des.to_dict(orient=\"list\")\n",
    "df_sale_des \n",
    "df_sale_des[('id', 'count')]  # 交易数 [571.0, 575.0, 597.0, 583.0]\n",
    "\n",
    "df_sale_des[('amount', 'mean')]  \n",
    "df_sale_des[('amount', 'min')] \n",
    "df_sale_des[('amount', 'max')]\n",
    "df_sale_des[('amount', '50%')] \n",
    "\n",
    "\n",
    "df_sale_des[('interests', 'mean')]  \n",
    "df_sale_des[('interests', 'min')] \n",
    "df_sale_des[('interests', 'max')]\n",
    "df_sale_des[('interests', '50%')] \n",
    "\n",
    "df_sale_des[('interests', 'mean')]  \n",
    "df_sale_des[('interests', 'min')] \n",
    "df_sale_des[('interests', 'max')]\n",
    "df_sale_des[('interests', '50%')] \n",
    "\n",
    "\n",
    "df_sale_sum  = grouped.sum()\n",
    "df_sale_sum = df_sale_sum.sort_index().to_dict(orient=\"list\")\n",
    "df_sale_sum[\"amount\"]  \n",
    "df_sale_sum[\"interests\"]\n",
    "df_sale_sum[\"quantity\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
